﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Configuration;
using System.Web.Mvc;
using Localization;
using SinBiota.Model.Business.AuthenticationServices.Interfaces;
using SinBiota.Model.Business.CoordinatesServices.Interfaces;
using SinBiota.Model.Business.MapServices.Datatypes;
using SinBiota.Model.Business.OccurrenceServices.Interfaces;
using SinBiota.Model.Business.PersistenceServices.Interfaces;
using SinBiota.Model.Entities;
using Sinbiota.Web.ActionFilters;
using Sinbiota.Web.Extensions;
using Sinbiota.Web.Models;
using MvcContrib.Pagination;

namespace Sinbiota.Web.Controllers
{
    public class OccurrenceController : BaseController
    {
        private readonly IEcosystemPersistenceServices _ecosystemPersistenceServices;
        private readonly IHabitatPersistenceServices _habitatPersistenceServices;
        private readonly IMicroHabitatPersistenceServices _microHabitatPersistenceServices;
        private readonly IOccurrencePersistenceServices _occurrencePersistenceServices;
        private readonly IProjectPersistenceServices _projectPersistenceServices;
        private readonly IUserPersistenceServices _userPersistenceServices;
        private readonly ICoordinatesConversion _coordinatesConversion;
        private readonly IPermissionServices _permissionServices;
        private readonly IDepositaryUnitPersistenceServices _depositaryUnitPersistenceServices;
        private readonly ITaxonPersistenceServices _taxonPersistenceServices;
        private readonly ITaxonomyServices _taxonomyServices;


        public OccurrenceController(IEcosystemPersistenceServices ecosystemPersistenceServices, IHabitatPersistenceServices habitatPersistenceServices, IMicroHabitatPersistenceServices microHabitatPersistenceServices, IOccurrencePersistenceServices occurrencePersistenceServices, IProjectPersistenceServices projectPersistenceServices, IUserPersistenceServices userPersistenceServices, ICoordinatesConversion coordinatesConversion, IPermissionServices permissionServices, IDepositaryUnitPersistenceServices depositaryUnitPersistenceServices, ITaxonPersistenceServices taxonPersistenceServices, ITaxonomyServices taxonomyServices)
        {
            _projectPersistenceServices = projectPersistenceServices;
            _taxonomyServices = taxonomyServices;
            _taxonPersistenceServices = taxonPersistenceServices;
            _depositaryUnitPersistenceServices = depositaryUnitPersistenceServices;
            _permissionServices = permissionServices;
            _coordinatesConversion = coordinatesConversion;
            _occurrencePersistenceServices = occurrencePersistenceServices;
            _microHabitatPersistenceServices = microHabitatPersistenceServices;
            _habitatPersistenceServices = habitatPersistenceServices;
            _ecosystemPersistenceServices = ecosystemPersistenceServices;
            _userPersistenceServices = userPersistenceServices;
        }

        // GET: /Occurrence
        [ImportMessagesFromTempData]
        public ActionResult Index()
        {
            return View();
        }
        //
        // GET: /Occurrence/Details/5

        [ImportMessagesFromTempData]
        public ActionResult Details(int id)
        {
            var occurrence = _occurrencePersistenceServices.FindById(id);
            if (occurrence == null)
            {
                return Request.IsAjaxRequest() ? Content(Strings.PageNotFound) : (ActionResult)View("NotFound");
            }
            // Found occurrence; build display model:
            var model = new OccurrenceDisplayModel(occurrence, _taxonPersistenceServices, _permissionServices.CanEditOccurrence(User.Identity.Name, occurrence));
            return Request.IsAjaxRequest() ? (ActionResult)PartialView("OccurrenceDetails", model) : View(model);

                
        }

        //
        // GET: /Occurrence/Create
        [Authorize]
        [ImportMessagesFromTempData]
        public ActionResult Create()
        {
//int? id
            //Test if it is a copy:
            var occurrenceId = Convert.ToInt32(Request.Params["occurrenceId"]);
            if (occurrenceId > 0)
            {
                var occurrence = _occurrencePersistenceServices.FindById(occurrenceId);
                return occurrence != null ? View(CreateOccurrenceFormModel(occurrence)) : View("NotFound");
            }
            // Test if project is already defined:
            var projectId = Convert.ToInt32(Request.Params["projectId"]);
            if (projectId > 0)
            {
                var project = _projectPersistenceServices.FindById(projectId);
                return _permissionServices.CanIncludeOccurrence(User.Identity.Name, project)
                           ? View(CreateOccurrenceFormModel(new Occurrence{Project = project}))
                           : View("NonAuthorized");
            }
            // Project not defined, choose one first:
            var projects = _userPersistenceServices.ActiveProjectsFromUser(User.Identity.Name);
            
            // if we have just one project, return the create occurrence form with this project
            if (projects.Count() == 1) return View(CreateOccurrenceFormModel(new Occurrence {Project = projects[0]}));
            
            // If user has more than 1 project, return list to choose from; 
            if (projects.Count() > 1) return View("ChooseProject", new ChooseProjectForm(projects));
            
            // if user is not in any project, return all active projects if user is admin, otherwise return error message;;
            return _permissionServices.IsAdmin(User.Identity.Name)
                       ? View("ChooseProject", new ChooseProjectForm(_projectPersistenceServices.FindAllActive()))
                       : View("Error", " ", Messages.NoProjectsAssociatedWithUser);
        }

        [Authorize]
        [HttpPost, ExportMessagesToTempData]
        public ActionResult Create(FormCollection collection, OccurrenceFormModel formModel, int projectId, int occurrenceAuthorId, int[] ecosystemId, int[] habitatId, int[] microHabitatId, string[] keywordField, int municipalityId, int conservationUnitId, int drainageBasinId, int[] specimenIdList, SpecimenModel[] specimens)
        {
            
            // check permission:
            var project = _occurrencePersistenceServices.FindProjectById(projectId);
            if (!_permissionServices.CanIncludeOccurrence(User.Identity.Name, project)) return View("NonAuthorized");

            // Create new occurrence and update model from form:
            var newOccurrence = new Occurrence { Project = project };
            TryUpdateModel(newOccurrence, "Occurrence");

            // Check if publication is empty:
            if (String.IsNullOrWhiteSpace(newOccurrence.BibliographicalReference.Title))
            {
                newOccurrence.BibliographicalReference = null;
                ModelState["Occurrence.BibliographicalReference.Title"].Errors.Clear();
            }

            if (ModelState.IsValid)
            {
                // Create occurrence:
                var username = User.Identity.Name;
                _occurrencePersistenceServices.SaveOrUpdate(newOccurrence, username, occurrenceAuthorId, municipalityId, conservationUnitId, drainageBasinId, ecosystemId, habitatId, microHabitatId, keywordField);

                // create specimens:
                UpdateSpecimens(newOccurrence, specimens, null);

                // include in spatial BD:
                _occurrencePersistenceServices.IncludeOccurrencesInSpatialDatabase(new List<Occurrence> {newOccurrence});

                // message and return
                SetInformationMessage(Messages.OperationCompletedSuccessfully);
                return RedirectToAction("Created", new {Id=newOccurrence.Id});
            }
            return View(CreateOccurrenceFormModel(newOccurrence));
        }

        //
        // GET: /Occurrence/Edit/5
        [Authorize]
        [ImportMessagesFromTempData]
        public ActionResult Edit(int id)
        {
            var occurrence = _occurrencePersistenceServices.FindById(id);
            if (occurrence == null) return View("NotFound");
            if (_permissionServices.CanEditOccurrence(User.Identity.Name, occurrence))
            {
                var model = CreateOccurrenceFormModel(occurrence);
                return View(model);
            }
            
            return View("NonAuthorized");
        }

        //
        // POST: /Occurrence/Edit/5
        [Authorize]
        [HttpPost, ExportMessagesToTempData]
        public ActionResult Edit(int id, FormCollection collection, OccurrenceFormModel formModel, int projectId, int occurrenceAuthorId, int[] ecosystemId, int[] habitatId, int[] microHabitatId, string[] keywordField, int municipalityId, int conservationUnitId, int drainageBasinId, SpecimenModel[] specimens, int[] deletedSpecimensId)
        {

            var editOccurrence = _occurrencePersistenceServices.FindById(id);

            // Check permission:
            if (!_permissionServices.CanEditOccurrence(User.Identity.Name, editOccurrence)) return View("NonAuthorized");

            // Try update validations:
            TryUpdateModel(editOccurrence, "Occurrence");

            // Check if publication is empty:
            if (String.IsNullOrWhiteSpace(editOccurrence.BibliographicalReference.Title))
            {
                editOccurrence.BibliographicalReference = null;
                ModelState["Occurrence.BibliographicalReference.Title"].Errors.Clear();
            }

            if (ModelState.IsValid)
            {
                // Treat specimens:
                UpdateSpecimens(editOccurrence, specimens, deletedSpecimensId);
                // Save occurrence:
                var username = User.Identity.Name;
                _occurrencePersistenceServices.SaveOrUpdate(editOccurrence, username, occurrenceAuthorId, municipalityId,
                                                      conservationUnitId, drainageBasinId, ecosystemId, habitatId,
                                                      microHabitatId, keywordField);
                SetInformationMessage(Messages.OperationCompletedSuccessfully);
                return RedirectToAction("Index");
            }
            return View(CreateOccurrenceFormModel(editOccurrence));
        }

        private void UpdateSpecimens(Occurrence occurrence, IEnumerable<SpecimenModel> specimens, IEnumerable<int> deletedSpecimensId)
        {
            // First, delete: 
            if (deletedSpecimensId != null)
            {
                foreach (var specimen in deletedSpecimensId.Select(specimenId => _occurrencePersistenceServices.FindSpecimenById(specimenId)))
                {
                    // remove from occurrence list: 
                    occurrence.SpecimenList.Remove(specimen);
                    // check records:
                    foreach (var depositaryUnitRecord in specimen.DepositaryUnitRecords)
                    {
                        _depositaryUnitPersistenceServices.DeleteRecord(depositaryUnitRecord);
                    }
                    // delete specimen:
                    _occurrencePersistenceServices.DeleteSpecimen(specimen);
                }
            }
            // Then create/update:

            if (specimens != null) foreach (var specimenModel in specimens) 
            {
                if (specimenModel.Id <= 0) //new specimen
                {
                    var specimen = new Specimen
                    {
                        Occurrence = occurrence,
                        Count = specimenModel.Count,
                        TaxonomicClassification =
                            new TaxonomicClassification
                            {
                                Taxon =
                                    _occurrencePersistenceServices.FindTaxonById(
                                        specimenModel.TaxonId)
                            }
                    };
                    _occurrencePersistenceServices.SaveSpecimen(specimen);
                    // Has record info? create record: 
                    CreateRecordIfNeeded(specimen, specimenModel.DepositaryUnit, specimenModel.RecordUniqueId);
                }
                else //updated specimen
                {
                    var specimen = _occurrencePersistenceServices.FindSpecimenById(specimenModel.Id);

                    var changed = false;
                    // check if taxonomy changed:
                    if (specimen.TaxonomicClassification.Taxon.Id != specimenModel.TaxonId)
                    {
                        specimen.TaxonomicClassification.Taxon = _occurrencePersistenceServices.FindTaxonById(specimenModel.TaxonId);
                        changed = true;
                    }
                    // check if count changed:
                    if (specimen.Count != specimenModel.Count)
                    {
                        specimen.Count = specimenModel.Count;
                        changed = true;
                    }
                    if (changed) _occurrencePersistenceServices.SaveSpecimen(specimen);

                    // check if it has record update id:
                    if (specimenModel.DepositaryUnitRecordId > 0) 
                    {
                        var depositaryUnitRecord = _depositaryUnitPersistenceServices.FindRecordById(specimenModel.DepositaryUnitRecordId);
                        // If depositary unit is null it means the user delete it:
                        if (String.IsNullOrWhiteSpace(specimenModel.DepositaryUnit))
                        {
                            _depositaryUnitPersistenceServices.DeleteRecord(depositaryUnitRecord);
                        }
                        else
                        {
                            depositaryUnitRecord.UniqueId = specimenModel.RecordUniqueId;
                            depositaryUnitRecord.DepositaryUnit = _depositaryUnitPersistenceServices.FindByExactNameOrCreate(specimenModel.DepositaryUnit);
                            _depositaryUnitPersistenceServices.SaveRecord(depositaryUnitRecord);
                        }
                    }
                    // Has new record info? create record: 
                    else CreateRecordIfNeeded(specimen, specimenModel.DepositaryUnit, specimenModel.RecordUniqueId);
                    
                }
            }
        }

        private void CreateRecordIfNeeded(Specimen specimen, string depositaryUnitName, string recordUniqueId)
        {
            if (!(!String.IsNullOrWhiteSpace(depositaryUnitName) & !String.IsNullOrWhiteSpace(recordUniqueId))) return;
            var depositaryUnit = _depositaryUnitPersistenceServices.FindByExactNameOrCreate(depositaryUnitName);
            var depositaryUnitRecord = new DepositaryUnitRecord { Specimen = specimen, UniqueId = recordUniqueId, DepositaryUnit = depositaryUnit };
            _depositaryUnitPersistenceServices.SaveRecord(depositaryUnitRecord);
        }

        // GET: /Occurrence/Copy/5
        [Authorize]
        [ImportMessagesFromTempData]
        public ActionResult Copy(int id)
        {
            var occurrence = _occurrencePersistenceServices.FindById(id);
            if (occurrence == null) return View("NotFound");

            if (!_permissionServices.CanCopyOccurrence(User.Identity.Name, occurrence)) 
            {
                return View("NonAuthorized");
            }
            return RedirectToAction("Create", new { occurrenceId = id });
        }

        //
        // GET: /Occurrence/Delete/5

        //public ActionResult Delete(int id)
        //{
        //    var occurrence = _occurrencePersistenceServices.FindById(id);
        //    return occurrence == null ? View("NotFound") : View(occurrence);
        //}

        //
        // POST: /Occurrence/Delete/5
        [Authorize]
        [HttpPost, ExportMessagesToTempData]
        public ActionResult Delete(int id, FormCollection collection)
        {
            var occurrence = _occurrencePersistenceServices.FindById(id);
            if (!_permissionServices.CanDeleteOccurrence(User.Identity.Name, occurrence)) 
            {
                return View("NonAuthorized");
            }

            _occurrencePersistenceServices.DeleteById(id);
            SetInformationMessage(Messages.OperationCompletedSuccessfully);
            return RedirectToAction("Index");

        }

        // SEARCH:
        private const int ResultsPerPage = 50;
        private const string AllOccurrencesKey = "AllOccurrences";

        [ImportMessagesFromTempData]
        public ActionResult Search(string pagedListGuid, int? page)
        {
            if (!String.IsNullOrEmpty(pagedListGuid))
            {
                var cacheService = new CachingService();
                var occurrences = cacheService.Get<IEnumerable<Occurrence>>(pagedListGuid) ?? new List<Occurrence>();
                if (occurrences.Count() == 0)
                {
                    if (pagedListGuid.Equals(AllOccurrencesKey)) return ListAll(page);
                    SetInformationMessage(Messages.SorrySearchTimeOut);
                    return View();
                }
                ViewData["PagedListGuid"] = pagedListGuid;
                var occurrenceList = new OccurrenceListModel(occurrences, _permissionServices, User.Identity.Name);
                return View("List", occurrenceList.AsPagination(page ?? 1, ResultsPerPage));
            }
            return View();
        }

        [HttpPost, ExportMessagesToTempData]
        public ActionResult Search(string searchField, int legacyTaxonomicGroupId, int municipalityId, int projectId, Ambient ambient, OccurrenceSearchOption occurrenceSearchOption)
        {
            var occurrences = FindOccurrencesBySearchType(searchField.Trim(), legacyTaxonomicGroupId, municipalityId, projectId, ambient, occurrenceSearchOption);
            return occurrences != null ? CachedPagedList(occurrences) : View("NotFound");
        }

        //
        // GET: /Occurrence/ListAll
        [ImportMessagesFromTempData]
        public ActionResult ListAll(int? page)
        {
            // cache:
            var cacheService = new CachingService();
            var occurrences = cacheService.Get<IEnumerable<Occurrence>>(AllOccurrencesKey);
            if (occurrences == null)
            {
                occurrences = _occurrencePersistenceServices.FindAll();
                cacheService.Insert(AllOccurrencesKey, occurrences);
            }
            ViewData["PagedListGuid"] = AllOccurrencesKey;
            var o = new OccurrenceListModel(occurrences, _permissionServices, User.Identity.Name);
            return View("List", o.AsPagination(page ?? 1, ResultsPerPage));
            //return View("List", occurrences.AsPagination(page ?? 1, ResultsPerPage));
        }


        private ActionResult CachedPagedList(IEnumerable<Occurrence> occurrences)
        {
            // cache:
            var cacheService = new CachingService();
            var guid = Guid.NewGuid().ToString();
            cacheService.Insert(guid, occurrences);
            ViewData["PagedListGuid"] = guid;
            var o = new OccurrenceListModel(occurrences, _permissionServices, User.Identity.Name);
            return View("List", o.AsPagination(1, ResultsPerPage));

//            return View("List", occurrences.AsPagination(1, ResultsPerPage));
        }
        private IEnumerable<Occurrence> FindOccurrencesBySearchType(string searchField, int legacyTaxonomicGroupId, int municipalityId, int projectId, Ambient ambient, OccurrenceSearchOption occurrenceSearchOption)
        {
            switch (occurrenceSearchOption)
            {
                case OccurrenceSearchOption.Taxon:
                    return _occurrencePersistenceServices.FindByTaxon(searchField);
                case OccurrenceSearchOption.Ambient:
                    return _occurrencePersistenceServices.FindByAmbient(ambient);
                case OccurrenceSearchOption.Author:
                    return _occurrencePersistenceServices.FindByAuthorName(searchField);
                case OccurrenceSearchOption.Keyword:
                    return _occurrencePersistenceServices.FindByKeyword(searchField);
                case OccurrenceSearchOption.Municipality:
                    return _occurrencePersistenceServices.FindByMunicipalityId(municipalityId);
                case OccurrenceSearchOption.ProjectCoordinator:
                    return _occurrencePersistenceServices.FindByProjectCoordinator(searchField);
                case OccurrenceSearchOption.ProjectMember:
                    return _occurrencePersistenceServices.FindByProjectMember(searchField);
                case OccurrenceSearchOption.Project:
                    return _occurrencePersistenceServices.FindByProjectId(projectId);
                case OccurrenceSearchOption.Publication:
                    return _occurrencePersistenceServices.FindByPublication(searchField);
                case OccurrenceSearchOption.TaxonomicGroup:
                    return _occurrencePersistenceServices.FindByTaxonomicGroupId(legacyTaxonomicGroupId);
                case OccurrenceSearchOption.Username:
                    return _occurrencePersistenceServices.FindByUsername(searchField);
                case OccurrenceSearchOption.Id:
                    return new List<Occurrence> {_occurrencePersistenceServices.FindById(int.Parse(searchField))};
            }
            return null;
        }

        // Casos de busca especiais (através de links diretos nas páginas)
        [ImportMessagesFromTempData]
        public ActionResult Project(int id)
        {
            return CachedPagedList(_occurrencePersistenceServices.FindByProjectId(id));
        }

        [ImportMessagesFromTempData]
        public ActionResult SearchKeyword(string searchField)
        {
            var occurrences = FindOccurrencesBySearchType(searchField, 0, 0, 0, Ambient.Terrestrial, OccurrenceSearchOption.Keyword);
            return occurrences != null ? CachedPagedList(occurrences) : View("NotFound");
        }

        [ImportMessagesFromTempData]
        [Authorize]
        public ActionResult CurrentUser()
        {
            return CachedPagedList( _occurrencePersistenceServices.FindByUsername(User.Identity.Name));
        }



        private OccurrenceFormModel CreateOccurrenceFormModel(Occurrence occurrence)
        {
            return new OccurrenceFormModel
                            {
                                Occurrence = occurrence,
                                SpecimenListModel = new SpecimenListModel(occurrence.SpecimenList),
                                EcosystemCheckboxGroup =
                                    new CheckboxGroup("ecosystemId", _ecosystemPersistenceServices.FindAll().ToCheckedItems(x => x.Name, x => x.Id, x => occurrence.Ecosystems.Contains(x))),
                                HabitatCheckboxGroup =
                                    new CheckboxGroup("habitatId", _habitatPersistenceServices.FindAll().ToCheckedItems(x => x.Name, x => x.Id, x => occurrence.Habitats.Contains(x))),
                                MicroHabitatCheckboxGroup =
                                    new CheckboxGroup("microHabitatId", _microHabitatPersistenceServices.FindAll().ToCheckedItems(x => x.Name, x => x.Id, x => occurrence.MicroHabitats.Contains(x))),
                                
                            };
        }

        [ImportMessagesFromTempData]
        public ActionResult SpeciesProfile(int id)
        {
            const int numTries = 3;
            var currentTry = 0;
            while (currentTry < numTries)
            {
                var response = WebRequest.Create(String.Format("http://eol.org/api/pages/1.0/{0}.json?common_names=1&details=1&images=75&subjects=all&text=1", id)).GetResponse();
                var streamReader = new StreamReader(response.GetResponseStream(), System.Text.Encoding.UTF8);
                var result = streamReader.ReadToEnd();
                streamReader.Close();
                response.Close();
                if (!String.IsNullOrWhiteSpace(result)) return View("SpeciesProfile", new SpeciesProfile(result));
                currentTry++;
            }
            return View("SpeciesProfile", new SpeciesProfile(id));
            
        }


        [ImportMessagesFromTempData]
        public ActionResult FullSpecimenTree()
        {
            return View(new SpecimenListFullModel(_occurrencePersistenceServices.FindAllSpecimen(), _taxonPersistenceServices, false));
        }



        /* AJAX Functions, callable from the map: */
        public JsonResult MapSearch(string term)
        {
            // Get search term (project id for now)
            var id = int.Parse(term);

            // Find occurrences:
            var occurrences = _occurrencePersistenceServices.FindByProjectId(id);
            // Build list of points (encoding for compression) and ids of occurrences:
            var points = _coordinatesConversion.EncodeLatLong(occurrences.Select(occurrence => new LatLong(occurrence.Location.Latitude, occurrence.Location.Longitude)).ToList());
            var ids = occurrences.Select(occurrence => occurrence.Id);

            // Return as a JSON object:
            return Json(new { ids, points });
        }

        public JsonResult AdvancedMapSearch(string searchField, string legacyTaxonomicGroupId, string municipalityId, string projectId, string ambient, string occurrenceSearchOption)
        {
            var result = (Ambient) Enum.GetValues(typeof (Ambient)).GetValue(int.Parse(ambient));
            var option = (OccurrenceSearchOption)Enum.GetValues(typeof(OccurrenceSearchOption)).GetValue(int.Parse(occurrenceSearchOption));
            // Caso especial: Taxon
            string points;
            IEnumerable<int> ids;
            if (option.Equals(OccurrenceSearchOption.Taxon))
            {
                var latLongWithIds = _occurrencePersistenceServices.GetOccurrencesByTaxon(searchField).Where(x => x.Latitude >= -90 && x.Latitude <= 90).ToList();
                points = _coordinatesConversion.EncodeLatLong(latLongWithIds.Select(x => new LatLong(x.Latitude, x.Longitude)).ToList());
                ids = latLongWithIds.Select(x => x.Id);
            }
            else
            {
                var occurrences = FindOccurrencesBySearchType(searchField, Int16.Parse(legacyTaxonomicGroupId), Int16.Parse(municipalityId), Int16.Parse(projectId), result, option).Where(x => x.Location.Latitude >= -90 && x.Location.Latitude <= 90);
                points = _coordinatesConversion.EncodeLatLong(occurrences.Select(occurrence => new LatLong(occurrence.Location.Latitude, occurrence.Location.Longitude)).ToList());
                ids = occurrences.Select(occurrence => occurrence.Id);
            }
            // Return as a JSON object:
            return Json(new { ids, points });
        }

        public JsonResult CachedMapSearch(string cacheKey)
        {
            var cacheService = new CachingService();
            var occurrences = cacheService.Get<IEnumerable<Occurrence>>(cacheKey).Where(x => x.Location.Latitude >= -90 && x.Location.Latitude <= 90);
            var points = _coordinatesConversion.EncodeLatLong(occurrences.Select(occurrence => new LatLong(occurrence.Location.Latitude, occurrence.Location.Longitude)).ToList());
            var ids = occurrences.Select(occurrence => occurrence.Id);
            // Return as a JSON object:
            return Json(new { ids, points });
            
        }
        public JsonResult SquareMapSearch(string nwLat, string nwLong, string seLat, string seLong)
        {

            var latLongWithIds = _occurrencePersistenceServices.GetOccurrencesInSquare(double.Parse(nwLat, CultureInfo.InvariantCulture),
                double.Parse(nwLong, CultureInfo.InvariantCulture), double.Parse(seLat, CultureInfo.InvariantCulture), double.Parse(seLong, CultureInfo.InvariantCulture));
            var points = _coordinatesConversion.EncodeLatLong(latLongWithIds.Select(x => new LatLong(x.Latitude, x.Longitude)).ToList());
            var ids = latLongWithIds.Select(x => x.Id);

            // Return as a JSON object:
            return Json(new { ids, points });
            
        }

        [ImportMessagesFromTempData]
        public ActionResult OccurrenceMapDetails(int id)
        {
            var occurrence = _occurrencePersistenceServices.FindById(id);
            return View("OccurrenceDetailsInfoBox", new OccurrenceDisplayModel(occurrence, _taxonPersistenceServices, _permissionServices.CanEditOccurrence(User.Identity.Name, occurrence)));
        }

        [ImportMessagesFromTempData]
        [Authorize]
        public ActionResult AddSpecimensFromFile(int id)
        {
            var occurrence = _occurrencePersistenceServices.FindById(id);
            if (!_permissionServices.CanEditOccurrence(User.Identity.Name, occurrence)) return View("NonAuthorized");
            return occurrence == null ? View("NotFound") : View(occurrence);
        }

        [HttpPost,ExportMessagesToTempData]
        [Authorize]
        public ActionResult AddSpecimensFromFile(int id, SpecimenFromSheet[] specimens)
        {
            //return Content(String.Join("<br>", specimens.Select(x => string.Format("ID: {0}  Append:{1} Ref:{2}", x.Id, x.AppendName,x.Reference))));

            var occurrence = _occurrencePersistenceServices.FindById(id);
            var user = _userPersistenceServices.FindByUsername(User.Identity.Name);
            if (!_permissionServices.CanEditOccurrence(user, occurrence)) return View("NonAuthorized");

            
            foreach (var specimen in specimens.Where(specimen => specimen.Id > 0)) // filter not found, with Id>0;
            {
                {
                    var taxon = _taxonPersistenceServices.FindById(specimen.Id);

                    if (!String.IsNullOrWhiteSpace(specimen.AppendName))
                    {
                        // verify if taxon was already included in the same occurrence, to prevent the creation of the same taxon more than once:
                        var alreadyIncludedTaxon = _taxonPersistenceServices.FindChildren(taxon).Where(x => x.Name.Equals(specimen.AppendName)).FirstOrDefault();
                        if (alreadyIncludedTaxon != null)
                        {
                            taxon = alreadyIncludedTaxon;
                        }
                        else {
                            // append a new taxon; first, save new reference:
                            var title = String.IsNullOrWhiteSpace(specimen.Reference)
                                            ? string.Format("Included in occurrence {0}, by user {1}.", id, User.Identity.Name)
                                            : specimen.Reference;
                            var taxonReference = new TaxonReference { Title = title };
                            _taxonPersistenceServices.SaveTaxonReference(taxonReference);
                            // then the taxon:
                            var newTaxon = new Taxon { Name = specimen.AppendName, Rank = specimen.AppendRank, Parent = taxon, TaxonReference = taxonReference, IncludedByUser = user};
                            _taxonPersistenceServices.Save(newTaxon);
                            taxon = newTaxon;
                        }

                    }
                    var newSpecimen = new Specimen { Occurrence = occurrence, TaxonomicClassification = new TaxonomicClassification {Taxon = taxon}, Count = specimen.Count};
                    _occurrencePersistenceServices.SaveSpecimen(newSpecimen);
                    // depositary unit:
                    if (!String.IsNullOrWhiteSpace(specimen.DepositaryUnit) && !String.IsNullOrWhiteSpace(specimen.RecordId))
                    {
                        var depositaryUnit = _depositaryUnitPersistenceServices.FindByExactNameOrCreate(specimen.DepositaryUnit);
                        var record = new DepositaryUnitRecord {DepositaryUnit = depositaryUnit, Specimen = newSpecimen, UniqueId = specimen.RecordId};
                        _depositaryUnitPersistenceServices.SaveRecord(record);
                    }
                }
            }
            SetInformationMessage(Messages.OperationCompletedSuccessfully);
            return RedirectToAction("Details", new {id});
            
        }


        //AJAX
        [Authorize]
        public JsonResult ExcelFileUpload(string qqfile)
        {
            var path = WebConfigurationManager.AppSettings["UploadFolder"];
            var prefix = DateTime.Now.ToString("yyyy-MM-dd_H-mm-ss_");
            string file;
            var stream = Request.InputStream;
            if (String.IsNullOrEmpty(Request["qqfile"]))
            {
                // IE
                HttpPostedFileBase postedFile = Request.Files[0];
                stream = postedFile.InputStream;
                file = Path.Combine(path, prefix + Path.GetFileName(Request.Files[0].FileName));
            }
            else
            {
                //Webkit, Mozilla
                file = Path.Combine(path, prefix + qqfile);
            }

            var buffer = new byte[stream.Length];
            stream.Read(buffer, 0, buffer.Length);
            System.IO.File.WriteAllBytes(file, buffer);
            
            // Read file:
            if (!Path.GetExtension(file).Equals(".xls"))
                return Json(JsonMessage.ErrorMessage(Messages.UnsupportedFileTypeOnlyXLS));

            var connection = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=Excel 8.0;", file));
            connection.Open();

            var sheetList = (connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows.Cast<DataRow>().Select
                (row => row["TABLE_NAME"].ToString())).Where(x => x.EndsWith("$") || x.EndsWith("$'")).
                Select(x => new { val = x, text = x.Replace("'", "").Replace("$", "") });

            connection.Close();
            return Json(JsonMessage.InformationMessage(Messages.FileUploadedSuccessfully, new {file, sheet_list = sheetList}), "text/html");    
            
		}

        //AJAX
        [Authorize]
        public JsonResult SelectWorksheet(string file, string sheet)
        {
            var connection = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=Excel 8.0;", file));
            connection.Open();

            var columns = (connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null).Rows.Cast<DataRow>()
                .Where(row => row[2].Equals(sheet))
                .OrderBy(row => row[6])
                .Select(row => new {val = row[6], text = row[3]})).ToList();

            return Json(JsonMessage.InformationMessage("", new { columns }));    

        }

        //AJAX
// ReSharper disable InconsistentNaming
        [Authorize]
        public JsonResult ParseExcel(string file, string sheet, string classification_type, int one_column_classification, int genus, int species, int subspecies, 
            int depositary_unit, string depositary_unit_source, string depositary_unit_text, int record_id, string number_of_individuals, int specimen_count)
// ReSharper restore InconsistentNaming
        {
            
            //return Content("dep:" + depositary_unit_source + " text:" + depositary_unit_text);
            // 0-based:
            genus--;
            species--;
            subspecies--;
            depositary_unit--;  
            record_id--;
            one_column_classification--;
            specimen_count--;
            // TODO: validation
            var connection = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=Excel 8.0;", file));
            connection.Open();
            var adapter = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", sheet), connection);
            var ds = new DataSet();
            adapter.Fill(ds, "specimen_table");
            var sw = new Stopwatch();
            sw.Start();

            var classificationParseList = new List<IDictionary<string,string>>();
            var depositaryUnitList = new List<string>();
            var recordIdList = new List<string>();
            var countList = new List<string>();
            foreach (var row in ds.Tables["specimen_table"].AsEnumerable())
            {
                // depositary unit:
                var depositaryUnitName = String.Empty;
                if (depositary_unit_source != null)
                {
                    if (depositary_unit_source.Equals("column") && depositary_unit >= 0)
                        depositaryUnitName = row[depositary_unit].ToString().Trim();
                    else if (depositary_unit_source.Equals("text"))
                        depositaryUnitName = depositary_unit_text.Trim();
                }
                depositaryUnitList.Add(depositaryUnitName);
                recordIdList.Add(record_id >= 0 ? row[record_id].ToString().Trim() : "");
                //count:
                if (number_of_individuals == "one") countList.Add("1");
                if (number_of_individuals == "unknown") countList.Add(null);
                if (number_of_individuals == "more")
                {
                    countList.Add(specimen_count>=0 ? row[specimen_count].ToString() : "unknown");
                }

                var dict = new Dictionary<string, string>();
                if (classification_type.Equals("different_columns"))
                {
                    if (genus < 0) throw new InvalidOperationException("Must choose genus column.");
                    var genusStr = row[genus].ToString().Trim();
                    // skip empty columns:
                    if (String.IsNullOrWhiteSpace(genusStr)) continue;
                    dict[Taxonomy.genero] = genusStr;
                    if (species > 0)
                    {
                        var speciesStr = row[species].ToString().Trim();
                        dict[Taxonomy.especie] = speciesStr;
                        if (subspecies > 0)
                        {
                            var subStr = row[subspecies].ToString().Trim();
                            dict[Taxonomy.subespecie] = subStr;
                        }
                    }
                }
                else
                {
                    var classification = row[one_column_classification].ToString().Trim();
                    // skip empty columns:
                    if (String.IsNullOrWhiteSpace(classification)) continue;
                    var split = classification.Split(' ').Where(x => !String.IsNullOrWhiteSpace(x)).ToArray();
                    dict[Taxonomy.genero] = split[0].Trim();
                    if (split.Length >=2)
                    {
                        dict[Taxonomy.especie] = split[1].Trim();
                    }
                    else if (split.Length >= 3)
                    {
                        dict[Taxonomy.subespecie] = split[2].Trim();
                    }
                }
                classificationParseList.Add(dict);
            }
            var resultList = _taxonomyServices.ParseClassification(classificationParseList);

            var jsonResult = new List<object>();

            for (var i = 0; i < resultList.Count; i++)
            {
                var similarityResult = resultList[i];
                var depositaryUnit = depositaryUnitList[i];
                var recordId = recordIdList[i];
                var count = countList[i];
                var fullname = String.Join(" ", similarityResult.ClassificationDictionary.Values);
                var classification =
                    similarityResult.ClassificationDictionary.Select(pair => new {rank = pair.Key, taxon = pair.Value});
                var options = similarityResult.TaxonSimilarities.Select( x => new SpecimenFromSheet
                            {
                                Name = x.Rank != "genus" ? x.Name : x.Name + " sp.",
                                Id = x.TaxonId,
                                Rank = x.Rank,
                                Append = false,
                            }).ToList();
                if (similarityResult.OriginalRank == Taxonomy.especie)
                    foreach (var similarity in similarityResult.TaxonSimilarities)
                    {
                        if (similarity.Rank == Taxonomy.genero)
                        {
                            // append_rank: just species for now; "species" is the COL term.
                            options.Add(new SpecimenFromSheet {
                                        Name = similarity.Name,
                                        Id = similarity.TaxonId,
                                        Rank = Taxonomy.especie,
                                        Append = true,
                                        AppendName = similarityResult.ClassificationDictionary[Taxonomy.especie],
                                        AppendRank = "species"
                                    });
                        }
                    }

                jsonResult.Add(new { fullname, classification, options, depositaryUnit, recordId, count, original_rank = similarityResult.OriginalRank });
            }
            sw.Stop();
            var classtime = sw.ElapsedMilliseconds;
            var message = String.Format(Messages.SpeciesClassificationFinishedInXSeconds, classtime/1000.0);
            return Json(JsonMessage.InformationMessage(message, new { file, list = jsonResult, classtime }));

        }

        [ImportMessagesFromTempData]
        [Authorize]
        public ActionResult Created(int id)
        {
            return View(id);
        }
    }
}
